﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ADOX;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Drawing;


namespace erse253_homework
{
    public class ImportExcel
    {
        /// <summary>
        /// Import a table from an excel file.
        /// </summary>
        /// <param name="file">
        /// Name of the file (path and name)
        /// </param>
        /// <param name="tabData">
        /// Name of the desired table
        /// </param>
        /// <returns>
        /// Table
        /// </returns>
        public DataTable Import(string file, string tabData)
        {

            if (file.Trim() != string.Empty)
            {
                try
                {
                    //get all the datatables inside the file
                    string[] strTables = GetTableExcel(file);

                    //search for the desired one.
                    foreach (String str in strTables)
                        if (!string.IsNullOrEmpty(str) && str == tabData + "$") 
                            return GetDataTableExcel(file, str);

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
                
            }

            return null;
        }

        /// <summary>
        /// Retrieve the data inside the table.
        /// </summary>
        /// <param name="strFileName">
        /// Name of the file (path and name)
        /// </param>
        /// <param name="Table">
        /// Name of the table
        /// </param>
        /// <returns>
        /// Returns a datatable structure.
        /// </returns>
        public static DataTable GetDataTableExcel(string strFileName, string Table)
        {
            //uses a conection to get the data.
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            adapter.Fill(ds);
            conn.Close();
            return ds.Tables[0];
        }

        /// <summary>
        /// Return the colection of tables inside the excel file
        /// </summary>
        /// <param name="strFileName">
        /// Full file name (path and name)
        /// </param>
        /// <returns>
        /// Names of the tables inside the excel file.
        /// </returns>
        public static string[] GetTableExcel(string strFileName)
        {

            //create a conection to an adodb interoperability service (this service is responsible for handling conection with office files throught a database conection)
            Catalog Catlog = new Catalog();
            ADOX.Table Table = new ADOX.Table();
            ADODB.Connection Conn = new ADODB.Connection();
            Conn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
            Catlog.ActiveConnection = Conn;

            //every table name will be stored inside the vector strTables.
            if (Catlog.Tables.Count > 0)
            {
                string[] strTables = new string[Catlog.Tables.Count];
                int item = 0;
                foreach (ADOX.Table tab in Catlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
                Conn.Close();
                return strTables;
            }
            Conn.Close();
            return null;
        }

    }
}
